package com.ueip.backend.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.ueip.backend.mapper.*;
import com.ueip.backend.pojo.*;
import com.ueip.backend.service.ClueService;
import com.ueip.backend.utils.IdentityAuthenticationUtil;
import com.ueip.backend.vo.ClueStatistics;
import com.ueip.backend.vo.PageCondition;
import com.ueip.backend.vo.Result;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.io.FileOutputStream;
import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.Year;
import java.time.ZoneId;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class ClueServiceImpl implements ClueService {
    @Resource
    private UserMapper userMapper;
    @Resource
    private ClueMapper clueMapper;

    @Resource
    private CityMapper cityMapper;

    @Resource
    private SchoolMapper schoolMapper;

    @Resource
    private PositionMapper positionMapper;

    @Resource
    private UserInfoMapper userInfoMapper;

    @Override
    public Result add(Map<String, String> data) {
        // 提取当前用户
        User curUser = IdentityAuthenticationUtil.getCurUser();
        try {
            Integer pid = Integer.parseInt(data.get("pid"));
            Integer cid = Integer.parseInt(data.get("cid"));
            BigInteger sid = new BigInteger(data.get("sid"));
            Integer uid = Integer.parseInt(data.get("uid"));
            Integer firstChoice = Integer.parseInt(data.get("firstChoice"));
            Integer firstMajor = Integer.parseInt(data.get("firstMajor"));
            Integer secondChoice = Integer.parseInt(data.get("secondChoice"));
            Integer secondMajor = Integer.parseInt(data.get("secondMajor"));
            String studentName = data.get("studentName");
            String qq = data.get("qq");
            String phoneNum = data.get("phoneNum");
            String email = data.get("email");
            String landline = data.get("landline");
            String admissionTicket = data.get("admissionTicket");
            String idNum = data.get("idNum");
            String address = data.get("address");
            String remark = data.get("remark");
            Integer candidateType = Integer.parseInt(data.get("candidateType"));

            int cnt = 0;
            if ( qq.length() != 0) cnt ++ ;
            if (phoneNum.length() != 0) cnt ++ ;
            if (email.length() != 0) cnt ++ ;
            if (landline.length() != 0) cnt ++ ;
            if (admissionTicket.length() != 0) cnt ++ ;
            if (cnt < 1) {
                return Result.error("联系方式至少填写一项");
            }

            if (studentName == null || studentName.length() == 0) {
                return Result.error("学生姓名不能为空");
            }

            if (curUser.getId() != uid) {
                return Result.error("非本人操作！");
            }

            if (cityMapper.selectOne(new QueryWrapper<City>().eq("id", cid)) == null) {
                return Result.error("城市不存在或已被删除");
            }

            if (cityMapper.selectOne(new QueryWrapper<City>().eq("id", pid)) == null) {
                return Result.error("省份不存在或已被删除");
            }

            if (schoolMapper.selectOne(new QueryWrapper<School>().eq("id", sid)) == null) {
                return Result.error("学校不存在或已被删除");
            }


            clueMapper.insert(new Clue(
                    null,
                    pid,
                    cid,
                    sid,
                    studentName,
                    uid,
                    qq,
                    phoneNum,
                    email,
                    landline,
                    admissionTicket,
                    idNum,
                    firstChoice,
                    firstMajor,
                    secondChoice,
                    secondMajor,
                    candidateType,
                    address,
                    remark,
                    new Date(),
                    null,
                    null,
                    null,
                    null,
                    null));
            return Result.ok("success");
        } catch (Exception e) {
            return Result.error("数据有误");
        }
    }

    @Override
    public Result update(Map<String, String> data) {
        // 提取当前用户
        User curUser = IdentityAuthenticationUtil.getCurUser();
        try {
            Integer id = Integer.parseInt(data.get("id"));
            Clue clue = clueMapper.selectById(id);
            if (clue == null) {
                return Result.error("该线索不存在或已被删除");
            }
            Integer pid = Integer.parseInt(data.get("pid"));
            Integer cid = Integer.parseInt(data.get("cid"));
            BigInteger sid = new BigInteger(data.get("sid"));
            Integer uid = Integer.parseInt(data.get("uid"));
            Integer firstChoice = Integer.parseInt(data.get("firstChoice"));
            Integer firstMajor = Integer.parseInt(data.get("firstMajor"));
            Integer secondChoice = Integer.parseInt(data.get("secondChoice"));
            Integer secondMajor = Integer.parseInt(data.get("secondMajor"));
            String studentName = data.get("studentName");
            String qq = data.get("qq");
            String phoneNum = data.get("phoneNum");
            String email = data.get("email");
            String landline = data.get("landline");
            String admissionTicket = data.get("admissionTicket");
            String idNum = data.get("idNum");
            String address = data.get("address");
            String remark = data.get("remark");
            Integer candidateType = Integer.parseInt(data.get("candidateType"));

            int cnt = 0;
            if (qq.length() != 0) cnt ++ ;
            if (phoneNum.length() != 0) cnt ++ ;
            if (email.length() != 0) cnt ++ ;
            if (landline.length() != 0) cnt ++ ;
            if (admissionTicket.length() != 0) cnt ++ ;
            if (cnt < 1) {
                return Result.error("联系方式至少填写一项");
            }

            if (studentName == null || studentName.length() == 0) {
                return Result.error("学生姓名不能为空");
            }

            if (curUser.getId() != uid) {
                return Result.error("非本人操作！");
            }

            if (cityMapper.selectOne(new QueryWrapper<City>().eq("id", cid)) == null) {
                return Result.error("城市不存在或已被删除");
            }

            if (cityMapper.selectOne(new QueryWrapper<City>().eq("id", pid)) == null) {
                return Result.error("省份不存在或已被删除");
            }

            if (schoolMapper.selectOne(new QueryWrapper<School>().eq("id", sid)) == null) {
                return Result.error("学校不存在或已被删除");
            }

            clueMapper.updateById(new Clue(
                    id,
                    pid,
                    cid,
                    sid,
                    studentName,
                    uid,
                    qq,
                    phoneNum,
                    email,
                    landline,
                    admissionTicket,
                    idNum,
                    firstChoice,
                    firstMajor,
                    secondChoice,
                    secondMajor,
                    candidateType,
                    address,
                    remark,
                    clue.getEntryTime(),
                    null,
                    null,
                    null,
                    null,
            null));
            return Result.ok("success");
        } catch (Exception e) {
            return Result.error("数据有误");
        }
    }

    @Override
    public Result deleteById(Map<String, String> data) {
        // 提取当前用户
        User curUser = IdentityAuthenticationUtil.getCurUser();
        try {
            Integer id = Integer.parseInt(data.get("id"));
            Clue clue = clueMapper.selectById(id);
            if (clue == null) {
                return Result.ok("该线索不存在或已被删除");
            }
            clueMapper.deleteById(clue);
            return Result.ok("success");
        } catch (Exception e) {
            return Result.error("数据有误");
        }
    }

    @Override
    public Result getAll() {
        // 提取当前用户
        User curUser = IdentityAuthenticationUtil.getCurUser();
        Map<Integer, String> cityMap = cityMapper.selectList(null).stream().collect(Collectors.toMap(City::getId, City::getName, (key1, key2) -> key1 ));
        Map<BigInteger, String> schoolMap = schoolMapper.selectList(null).stream().collect(Collectors.toMap(School::getId, School::getName, (key1, key2) -> key1 ));
        List<Clue> clues = clueMapper.selectList(null);
        for (Clue clue : clues) {
            clue.setPName(cityMap.get(clue.getPid()));
            clue.setCName(cityMap.get(clue.getCid()));
            clue.setSName(schoolMap.get(clue.getSid()));
        }
        return Result.ok(clues);
    }

    @Override
    public Result getClueByConditions(Map<String, String> data) {
        User curUser = IdentityAuthenticationUtil.getCurUser();
        try {
            String yearStr = data.get("year");
            String pidStr = data.get("pid");
            String cidStr = data.get("cid");
            String sidStr = data.get("sid");
            String studentName = data.get("studentName");
            QueryWrapper<Clue> queryWrapper = new QueryWrapper<>();
            if (yearStr != null && yearStr.length() != 0) {
                Integer year = Integer.parseInt(yearStr);
                queryWrapper.apply("YEAR(entry_time) = {0}", Year.of(year).getValue());
            }
            if (pidStr != null && pidStr.length() != 0) {
                Integer pid = Integer.parseInt(pidStr);
                queryWrapper.eq("pid", pid);
            }
            if (cidStr != null && cidStr.length() != 0) {
                Integer cid = Integer.parseInt(cidStr);
                queryWrapper.eq("cid", cid);
            }
            if (sidStr != null && sidStr.length() != 0) {
                Integer sid = Integer.parseInt(sidStr);
                queryWrapper.eq("sid", sid);
            }
            // 对名字进行模糊搜索
            if (studentName != null && studentName.length() != 0) {
                queryWrapper.like("student_name", studentName);
//                queryWrapper.lambda().like(Clue::getStudentName, studentName);
            }
            Map<Integer, String> cityMap = cityMapper.selectList(null).stream().collect(Collectors.toMap(City::getId, City::getName, (key1, key2) -> key1 ));
            Map<BigInteger, String> schoolMap = schoolMapper.selectList(null).stream().collect(Collectors.toMap(School::getId, School::getName, (key1, key2) -> key1 ));
            List<Clue> clues = clueMapper.selectList(queryWrapper);
            for (Clue clue : clues) {
                clue.setPName(cityMap.get(clue.getPid()));
                clue.setCName(cityMap.get(clue.getCid()));
                clue.setSName(schoolMap.get(clue.getSid()));
            }
            return Result.ok(clues);
        } catch (Exception e) {
            return Result.error("查询条件有误");
        }
    }

    @Override
    public Result getClueByConditions2(Map<String, String> data) {
        User curUser = IdentityAuthenticationUtil.getCurUser();
        try {
            Map<Integer, String> cityMap = cityMapper.selectList(null).stream().collect(Collectors.toMap(City::getId, City::getName, (key1, key2) -> key1));
            Map<BigInteger, String> schoolMap = schoolMapper.selectList(null).stream().collect(Collectors.toMap(School::getId, School::getName, (key1, key2) -> key1));
            String pidStr = data.get("pid");
            String cidStr = data.get("cid");
            String sidStr = data.get("sid");
            String offsetStr = data.get("offset");
            String pageSizeStr = data.get("pageSize");

            PageCondition pc = new PageCondition(
                    data.get("year"),
                    pidStr == "" ? null : Integer.parseInt(pidStr),
                    cidStr == "" ? null : Integer.parseInt(cidStr),
                    sidStr == "" ? null : Integer.parseInt(sidStr),
                    data.get("studentName"),
                    offsetStr == "" ? null : Integer.parseInt(offsetStr),
                    pageSizeStr == "" ? null : Integer.parseInt(pageSizeStr)
            );
            List<Clue> clues = clueMapper.selectByConditions(pc);
            for (Clue clue : clues) {
                clue.setPName(cityMap.get(clue.getPid()));
                clue.setCName(cityMap.get(clue.getCid()));
                clue.setSName(schoolMap.get(clue.getSid()));
            }
            return Result.ok(clues);
        }
        catch ( Exception e) {
            return Result.error("数据有误");
        }
    }

    @Override
    public Result getClueByConditions2Num(Map<String, String> data) {
        User curUser = IdentityAuthenticationUtil.getCurUser();

        return Result.ok(clueMapper.selectByConditionsNum(data));
    }

    @Override
    public Result setReported(Map<String, String> data) {
        User curUser = IdentityAuthenticationUtil.getCurUser();
        try {
            Integer id = Integer.parseInt(data.get("id"));
            List<Clue> clues = clueMapper.selectList(new QueryWrapper<Clue>().eq("id", id));
            if (clues.isEmpty()) {
                return Result.error("当前线索不存在或已被删除");
            }
            Clue clue = clues.get(0);
            if (clue.getReported() != null && clue.getReported() == 1) {
                return Result.error("当前线索已经报道，请勿重复操作");
            }
            clue.setReported(1);
            clue.setReportedTime(new Date());

            clueMapper.updateById(clue);
            return Result.ok("update success");
        } catch (Exception e) {
            return Result.error("数据有误");
        }
    }

    @Override
    public Result unsetReported(Map<String, String> data) {
        User curUser = IdentityAuthenticationUtil.getCurUser();
        try {
            Integer id = Integer.parseInt(data.get("id"));
            QueryWrapper<Clue> clueQueryWrapper = new QueryWrapper<Clue>().eq("id", id);
            List<Clue> clues = clueMapper.selectList(clueQueryWrapper);
            if (clues.isEmpty()) {
                return Result.error("当前线索不存在或已被删除");
            }
            Clue clue = clues.get(0);
            if (clue.getReported() == null || clue.getReported() == 0) {
                return Result.error("当前线索未报到报道，无法操作");
            }
            clue.setReported(0);
            clue.setReportedTime(null);
            clueMapper.update(clue, clueQueryWrapper);
            return Result.ok("update success");
        } catch (Exception e) {
            return Result.error("数据有误");
        }
    }

    @Override
    public Result statisticsByConditions(Map<String, String> data) {
        User curUser = IdentityAuthenticationUtil.getCurUser();
        try {
            return Result.ok(clueMapper.countStatisticsByConditions(data));
        } catch (Exception e) {
            return Result.error("数据有误");
        }
    }

    // 省名 总数/通知书数量/报道数量 用户名/职位/总数/通知书数量/报道数量
    @Override
    public Result generateProvCluesStatistic(Map<String, String> data) {
        User curUser = IdentityAuthenticationUtil.getCurUser();
        System.out.println(data);
        try {
            Integer year = Integer.parseInt(data.get("year"));
            Integer pid = Integer.parseInt(data.get("pid"));
            Integer pageSize = Integer.parseInt(data.get("pageSize"));
            Integer curPageNum = Integer.parseInt(data.get("curPageNum"));
//            String cid = data.get("cid");

            // 先查省里所有的用户id集合
            QueryWrapper<Clue> queryWrapper1 = new QueryWrapper<>();
            queryWrapper1.eq("pid", pid);
            List<Clue> clues = clueMapper.selectList(queryWrapper1);
            Set<Integer> uIdSet = new HashSet<>();
            for (Clue clue : clues) {
                uIdSet.add(clue.getUid());
            }

            // 查询省名称
            String pName = cityMapper.selectById(pid).getName();

            // 查询省所有线索数量
            QueryWrapper<Clue> queryWrapper3 = new QueryWrapper<>();
            queryWrapper3.eq("pid", pid);
            if (!year.equals("")) {
                queryWrapper3.apply("YEAR(entry_time) = {0}", Year.of(year).getValue());
            }
            Long pAllClueNum = clueMapper.selectCount(queryWrapper3);

            // 查询省所有通知书数量
            QueryWrapper<Clue> queryWrapper4 = new QueryWrapper<>();
            queryWrapper4.eq("pid", pid);
            queryWrapper4.eq("notification", 1);
            if (!year.equals("")) {
                queryWrapper4.apply("YEAR(entry_time) = {0}", Year.of(year).getValue());
            }
            Long pNotNum = clueMapper.selectCount(queryWrapper4);

            // 查询省所有报道数量
            QueryWrapper<Clue> queryWrapper5 = new QueryWrapper<>();
            queryWrapper5.eq("pid", pid);
            if (!year.equals("")) {
                queryWrapper5.apply("YEAR(entry_time) = {0}", Year.of(year).getValue());
            }
            queryWrapper5.eq("reported", 1);
            Long pRepNum = clueMapper.selectCount(queryWrapper5);

            List<ClueStatistics> clueStatistics = new LinkedList<>();

            // 对每一个用户进行查询（线索总数，通知数，报道数）
            for (Integer id : uIdSet) {
                ClueStatistics cs = new ClueStatistics();

                cs.setPName(pName);
                cs.setPAllClueNum(Math.toIntExact(pAllClueNum));
                cs.setPNotNum(Math.toIntExact(pNotNum));
                cs.setPRepNum(Math.toIntExact(pRepNum));

                // 先查用户名
                User user = userMapper.selectById(id);
                String username = user.getUsername();

                // 再查职位名
                QueryWrapper<UserInfo> userInfoQueryWrapper = new QueryWrapper<>();
                userInfoQueryWrapper.eq("uid", id);
                UserInfo userInfo = userInfoMapper.selectOne(userInfoQueryWrapper);
                String positionName = positionMapper.selectById(userInfo.getPositionId()).getName();

                // 用户 线索总数
                QueryWrapper<Clue> queryWrapper2 = new QueryWrapper<>();
                queryWrapper2.eq("uid", id);
                queryWrapper2.eq("pid", pid);
                if (!year.equals("")) {
                    queryWrapper2.apply("YEAR(entry_time) = {0}", Year.of(year).getValue());
                }
                long uAllClueNum = clueMapper.selectCount(queryWrapper2);

                // 用户 通知书总数
                QueryWrapper<Clue> queryWrapper7 = new QueryWrapper<>();
                queryWrapper7.eq("uid", id);
                queryWrapper7.eq("pid", pid);
                if (!year.equals("")) {
                    queryWrapper7.apply("YEAR(entry_time) = {0}", Year.of(year).getValue());
                }
                queryWrapper7.eq("notification", 1);
                long uNotNum = clueMapper.selectCount(queryWrapper7);


                // 用户 报道总数
                QueryWrapper<Clue> queryWrapper6 = new QueryWrapper<>();
                queryWrapper6.eq("uid", id);
                queryWrapper6.eq("pid", pid);
                if (!year.equals("")) {
                    queryWrapper6.apply("YEAR(entry_time) = {0}", Year.of(year).getValue());
                }
                queryWrapper6.eq("reported", 1);
                long uRepNum = clueMapper.selectCount(queryWrapper6);

                // 设置
                cs.setUsername(username);
                cs.setPositionName(positionName);
                cs.setUAllClueNum(Math.toIntExact(uAllClueNum));
                cs.setUNotNum(Math.toIntExact(uNotNum));
                cs.setURepNum(Math.toIntExact(uRepNum));

                clueStatistics.add(cs);
            }
            Integer fromIndex = (curPageNum - 1) * pageSize;
            if (fromIndex > clueStatistics.size() - 1) {
                return Result.error("页面数或页面容量过大");
            }
            Integer toIndex = curPageNum * pageSize - 1;
            if (toIndex > clueStatistics.size()) {
                toIndex = clueStatistics.size();
            }
//        System.out.println(fromIndex + " " + toIndex);
            return Result.ok(String.valueOf(clueStatistics.size()), clueStatistics.subList(fromIndex, toIndex));
        } catch (Exception e) {
            return Result.error("数据有误");
        }
    }

    // 省名\城市名\学校名 总数/通知书数量/报道数量 用户名/职位/总数/通知书数量/报道数量
    @Override
    public Result generateProvCluesStatistic2(Map<String, String> data) {
        User curUser = IdentityAuthenticationUtil.getCurUser();
        try {
            String yearStr = data.get("year");
            String pidStr = data.get("pid");
            String cidStr = data.get("cid");
            String sidStr = data.get("sid");
            Integer pageSize = Integer.parseInt(data.get("pageSize"));
            Integer curPageNum = Integer.parseInt(data.get("curPageNum"));

            int cnt = 7;
            List<QueryWrapper<Clue>> queryWrapperList = new ArrayList<>();
            for (int i = 0; i < cnt; i ++ ) {
                queryWrapperList.add(new QueryWrapper<Clue>());
            }
            String pName = "";
            // 判断是查学校还是城市还是省份
            // 并添加对应的查询条件
            if (sidStr != null && !"".equals(sidStr)) {
                Integer sid = Integer.parseInt(sidStr);
                for (int i = 0; i < cnt; i++) {
                    queryWrapperList.get(i).eq("sid", sid);
                }
                pName = schoolMapper.selectById(sid).getName();
            } else if (cidStr != null && !"".equals(cidStr)) {
                Integer cid = Integer.parseInt(cidStr);
                for (int i = 0; i < cnt; i ++ ) {
                    queryWrapperList.get(i).eq("cid", cid);
                }
                pName = cityMapper.selectById(cid).getName();
            } else if (pidStr != null && !"".equals(pidStr)) {
                Integer pid = Integer.parseInt(pidStr);
                for (int i = 0; i < cnt; i ++ ) {
                    queryWrapperList.get(i).eq("pid", pid);
                }
                pName = cityMapper.selectById(pid).getName();
            }

            // 使用 queryWrapperList[0] 查询负责省份\城市\学校的所有用户Id
            List<Clue> clues1 = clueMapper.selectList(queryWrapperList.get(0));
            Set<Integer> uIdSet = new HashSet<>();
            for (Clue clue : clues1) {
                uIdSet.add(clue.getUid());
            }

            // 添加年份条件
            if (yearStr != null && !"".equals(yearStr)) {
                Integer year = Integer.parseInt(yearStr);
                for (int i = 0; i < cnt; i ++ ) {
                    queryWrapperList.get(i).apply("YEAR(entry_time) = {0}", Year.of(year).getValue());
                }
            }

            // 使用 queryWrapperList[1] 查 省份\城市\学校 所有的线索
            Long pAllClueNum = clueMapper.selectCount(queryWrapperList.get(1));

            // 使用 queryWrapperList[2] 查 省份\城市\学校 所有发了通知书的线索
            queryWrapperList.get(2).eq("notification", 1);
            Long pNotNum = clueMapper.selectCount(queryWrapperList.get(2));

            // 使用 queryWrapperList[3] 查 省份\城市\学校 所有报道了的线索
            queryWrapperList.get(3).eq("reported", 1);
            Long pRepNum = clueMapper.selectCount(queryWrapperList.get(3));

            List<ClueStatistics> clueStatistics = new LinkedList<>();

            // 对每一个用户进行查询（线索总数，通知数，报道数）
            for (Integer id : uIdSet) {
                // 清空4~6的条件
                for (int i = 4; i < cnt; i ++ ) {
                    queryWrapperList.get(i).clear();
                }
                // 重新构造4~6的前提条件
                if (sidStr != null && !"".equals(sidStr)) {
                    Integer sid = Integer.parseInt(sidStr);
                    for (int i = 4; i < cnt; i++) {
                        queryWrapperList.get(i).eq("sid", sid);
                    }
                    pName = schoolMapper.selectById(sid).getName();
                } else if (cidStr != null && !"".equals(cidStr)) {
                    Integer cid = Integer.parseInt(cidStr);
                    for (int i = 4; i < cnt; i ++ ) {
                        queryWrapperList.get(i).eq("cid", cid);
                    }
                    pName = cityMapper.selectById(cid).getName();
                } else if (pidStr != null && !"".equals(pidStr)) {
                    Integer pid = Integer.parseInt(pidStr);
                    for (int i = 4; i < cnt; i ++ ) {
                        queryWrapperList.get(i).eq("pid", pid);
                    }
                    pName = cityMapper.selectById(pid).getName();
                }
                if (yearStr != null && !"".equals(yearStr)) {
                    Integer year = Integer.parseInt(yearStr);
                    for (int i = 4; i < cnt; i ++ ) {
                        queryWrapperList.get(i).apply("YEAR(entry_time) = {0}", Year.of(year).getValue());
                    }
                }

                // 构造新的统计数据
                ClueStatistics cs = new ClueStatistics();

                cs.setPName(pName);
                cs.setPAllClueNum(Math.toIntExact(pAllClueNum));
                cs.setPNotNum(Math.toIntExact(pNotNum));
                cs.setPRepNum(Math.toIntExact(pRepNum));

                // 先查用户名
                QueryWrapper<UserInfo> userInfoQueryWrapper = new QueryWrapper<>();
                userInfoQueryWrapper.eq("uid", id);
                UserInfo userInfo = userInfoMapper.selectOne(userInfoQueryWrapper);
                String username = userInfo.getName();

                // 再查职位名
                String positionName = positionMapper.selectById(userInfo.getPositionId()).getName();

                // 使用 queryWrapperList[4] 查 用户 省份\城市\学校 线索总数
                queryWrapperList.get(4).eq("uid", id);
                long uAllClueNum = clueMapper.selectCount(queryWrapperList.get(4));

                // 使用 queryWrapperList[5] 查 用户 省份\城市\学校 通知书线索总数
                queryWrapperList.get(5).eq("uid", id);
                queryWrapperList.get(5).eq("notification", 1);
                long uNotNum = clueMapper.selectCount(queryWrapperList.get(5));


                // 使用 queryWrapperList[6] 查 用户 省份\城市\学校 报道线索总数
                queryWrapperList.get(6).eq("uid", id);
                queryWrapperList.get(6).eq("reported", 1);
                long uRepNum = clueMapper.selectCount(queryWrapperList.get(6));

                // 设置
                cs.setUsername(username);
                cs.setPositionName(positionName);
                cs.setUAllClueNum(Math.toIntExact(uAllClueNum));
                cs.setUNotNum(Math.toIntExact(uNotNum));
                cs.setURepNum(Math.toIntExact(uRepNum));
                if (uAllClueNum != 0) {
                    clueStatistics.add(cs);
                }
            }
            if (clueStatistics.size() == 0) {
                return Result.ok("0", null);
            }
            Integer fromIndex = (curPageNum - 1) * pageSize;
            if (fromIndex > clueStatistics.size() - 1) {
                return Result.error("页面数或页面容量过大");
            }
            Integer toIndex = curPageNum * pageSize - 1;
            if (toIndex > clueStatistics.size()) {
                toIndex = clueStatistics.size();
            }
            return Result.ok(String.valueOf(clueStatistics.size()), clueStatistics.subList(fromIndex, toIndex));
        } catch (Exception e) {
            return Result.error("数据有误");
        }
    }

    @Override
    public Result addNotification(Map<String, String> data) {
        User curUser = IdentityAuthenticationUtil.getCurUser();
        System.out.println(data);
        try {
            Integer year = Integer.parseInt(data.get("year"));
            Integer provId = Integer.parseInt(data.get("provId"));
            Integer clueId = Integer.parseInt(data.get("clueId"));
            Clue clue = clueMapper.selectById(clueId);
            if (clue == null) {
                return Result.error("该线索不存在或已被删除");
            }
            if (clue.getNotification() != null && clue.getNotification() == 1) {
                return Result.error("该线索已生成通知书，请勿重复操作");
            }
            ZoneId timeZone = ZoneId.systemDefault();
            LocalDate getLocalDate = clue.getEntryTime().toInstant().atZone(timeZone).toLocalDate();
            if (getLocalDate.getYear() != year) {
                return Result.error("该线索创建时间与年分不同");
            }

            QueryWrapper<Clue> queryWrapper = new QueryWrapper<>();
            queryWrapper.eq("pid", provId);
            queryWrapper.apply("YEAR(entry_time) = {0}", Year.of(year).getValue());
            queryWrapper.eq("notification", 1);
            Long num = clueMapper.selectCount(queryWrapper);
            num ++ ;

            String numStr = num.toString();
            while (numStr.length() < 4) {
                numStr = "0" + numStr;
            }

            String notificationNumber = year.toString() + provId.toString().substring(0, 2) + numStr;
            clue.setNotification(1);
            clue.setNotificationNumber(notificationNumber);
            clue.setNotificationCreateTime(new Date());
            clueMapper.updateById(clue);
            return Result.ok("add success");
        } catch (Exception e) {
            return Result.error("数据有误");
        }
    }

    @Override
    public Result notificationDeleteById(Map<String, String> data) {
        User curUser = IdentityAuthenticationUtil.getCurUser();
        try {
            Integer id = Integer.parseInt(data.get("id"));
            Clue clue = clueMapper.selectById(id);
            if (clue == null) {
                return Result.error("该线索不存在或已被删除");
            }
            if (clue.getNotification() != 1) {
                return Result.error("该线索对应的通知书不存在或已被删除");
            }
            clue.setNotification(0);
            clue.setNotificationNumber(null);
            clue.setNotificationCreateTime(null);
            clueMapper.updateById(clue);
            return Result.ok("delete success");
        } catch (Exception e) {
            return Result.error("数据有误");
        }
    }

    @Override
    public Result notificationSelectByCondition(Map<String, String> data) {
        User curUser = IdentityAuthenticationUtil.getCurUser();
        try {
            Integer year = Integer.parseInt(data.get("year"));
            Integer provId = Integer.parseInt(data.get("provId"));
            QueryWrapper<Clue> queryWrapper = new QueryWrapper<>();
            queryWrapper.eq("pid", provId);
            queryWrapper.apply("YEAR(entry_time) = {0}", Year.of(year).getValue());
            return Result.ok(clueMapper.selectList(queryWrapper));
        } catch (Exception e) {
            return Result.error("数据有误");
        }
    }

    @Override
    public Result generateExcel(Map<String, String> data) {
        try {
            String yearStr = data.get("year");
            String pidStr = data.get("pid");
            String cidStr = data.get("cid");
            String sidStr = data.get("sid");
            QueryWrapper<Clue> clueQueryWrapper = new QueryWrapper<>();
            if (yearStr != null && !"".equals(yearStr)) {
                Integer year = Integer.parseInt(yearStr);
                clueQueryWrapper.apply("YEAR(entry_time) = {0}", Year.of(year).getValue());
            }
            if (pidStr != null && !"".equals(pidStr)) {
                Integer pid = Integer.parseInt(pidStr);
                clueQueryWrapper.eq("pid", pid);
            }
            if (cidStr != null && !"".equals(cidStr)) {
                Integer cid = Integer.parseInt(cidStr);
                clueQueryWrapper.eq("cid", cid);
            }
            if (sidStr != null && !"".equals(sidStr)) {
                Integer sid = Integer.parseInt(sidStr);
                clueQueryWrapper.eq("sid", sid);
            }

            List<Clue> clueList = clueMapper.selectList(clueQueryWrapper);

            // 创建工作簿和Sheet
            XSSFWorkbook workbook = new XSSFWorkbook();
            org.apache.poi.ss.usermodel.Sheet sheet = workbook.createSheet("Sheet1");

            // 创建标题行
            Row headerRow = sheet.createRow(0);
            String[] columnLabels = {"姓名", "QQ", "电话号码", "邮箱", "座机", "准考证号", "身份证号", "地址", "备注", "录入时间", "通知书号"};
            for (int i = 0; i < columnLabels.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(columnLabels[i]);
            }

            // 填充数据行
            int rowNum = 1;
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            for (Clue clue : clueList) {
                Row dataRow = sheet.createRow(rowNum);
                dataRow.createCell(0).setCellValue(clue.getStudentName());
                dataRow.createCell(1).setCellValue(clue.getQq());
                dataRow.createCell(2).setCellValue(clue.getPhoneNum());
                dataRow.createCell(3).setCellValue(clue.getEmail());
                dataRow.createCell(4).setCellValue(clue.getLandline());
                dataRow.createCell(5).setCellValue(clue.getAdmissionTicket());
                dataRow.createCell(6).setCellValue(clue.getIdNum());
                dataRow.createCell(7).setCellValue(clue.getAddress());
                dataRow.createCell(8).setCellValue(clue.getRemark());
                dataRow.createCell(9).setCellValue(dateFormat.format(clue.getEntryTime()));
                dataRow.createCell(10).setCellValue(clue.getNotificationNumber());
                rowNum++;
            }

            // 将数据写入输出流
            try (FileOutputStream outputStream = new FileOutputStream("output.xlsx")) {
                workbook.write(outputStream);
            }

            return Result.ok(workbook);

        } catch (Exception e) {
            return Result.error("数据有误");
        }
    }
}
